package zozopo.operations.business;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;


import zozopo.database.BusiDatabaseManager;
import zozopo.operations.comm.OperatingService;

public class AdOS extends OperatingService{ 
	private static final Log logger = LogFactory.getLog(AdOS.class);
	public AdOS(Connection conn){
		super(conn);
	}
	
	public AdOS(){
		this.databaseConnection = new BusiDatabaseManager();
		this.conn = databaseConnection.getConnection();
	}
	
	
	
	/**
	 * @version 1.0
	 * @see releaseConnection()
	 */
	public void releaseConnection(){
		databaseConnection.releaseConnection(conn);
	}

	/**
	 * @version 1.0
	 * @see 
	 */
	public List<AdTB> selectAdBySql(String sql){
		List<AdTB> adList = null;
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		
		if(resultSet != null){
				try {
					adList = new ArrayList<AdTB>();
					while(resultSet.next()){
						AdTB ad = new AdTB();
						ad.setId_ad(resultSet.getInt(AdTB.ID_AD));
						ad.setImage_ad(resultSet.getString(AdTB.IMAGE_AD));
						ad.setLocation_ad(resultSet.getString(AdTB.LOCATION_AD));
						ad.setName_ad(resultSet.getString(AdTB.NAME_AD));
						ad.setUrl_ad(resultSet.getString(AdTB.URL_AD));
						
						adList.add(ad);
					}
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		return adList;
	}
	
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<AdTB> selectAdByLocation(String location){
		List<AdTB> adList = null;
		String sql = "SELECT * FROM " + AdTB.TABLE_NAME + " WHERE " + AdTB.LOCATION_AD + "='" + location +"'";
		
		adList = selectAdBySql(sql);
		
		return adList;
	}
	
	
	public AdTB getAdById(String id){
		
		String sql = "select * from "+AdTB.TABLE_NAME+" where "+AdTB.ID_AD+"="+id;
		
		if(logger.isDebugEnabled()){
			logger.debug("getAdUrlById() sql:"+sql.toString());
		}
		
		ResultSet resultSet = databaseConnection.executeQuery(conn,sql);
		AdTB ad = null;
		try {
			if(resultSet.next()){
				
				ad = new AdTB();
				ad.setId_ad(resultSet.getInt(AdTB.ID_AD));
				ad.setName_ad(resultSet.getString(AdTB.NAME_AD));
				ad.setUrl_ad(resultSet.getString(AdTB.URL_AD));
				ad.setImage_ad(resultSet.getString(AdTB.ID_AD));
				ad.setLocation_ad(resultSet.getString(AdTB.LABLE_AD));
				ad.setKind_ad(resultSet.getString(AdTB.KIND_AD));
				ad.setLable_ad(resultSet.getString(AdTB.LABLE_AD));
			}
			resultSet.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return ad;
	}
	
	public String getAdUrlById(String id){
		String url = "";
		String sql = "SELECT * FROM " + AdTB.TABLE_NAME + " WHERE " + AdTB.ID_AD + "=" + id;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		try {
			if(resultSet.next()){
				url = resultSet.getString(AdTB.URL_AD);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return url;
	}
	
	public void views(String id){
		String sql = "update "+AdTB.TABLE_NAME+" set " +AdTB.VIEWS_AD + " = " + AdTB.VIEWS_AD + "+1 where "+ AdTB.ID_AD + " = " +id;
		databaseConnection.executeUpdate(conn, sql);
	}
}
